/*

Input file: Umrechnung_Wahlen_2013-2020_auf_LTW2018.xlsx [raw correspondences from statistical office MUC]
			
Output file: tmp/convkeys_ltw2018.dta [Conversion keys]

Main tasks: prep conversion keys for harmonizing precinct boundaries of all 
				election since 2013 to the boundaries of SE-18
	

Notes: 	
	> Basis for conversions are all eligible voters in election X, i.e. 
			Germans 18+ in FE/SE, Germans and EU-Foreigners in EuE and ME.
	> This is only relevant for precinct-level variables such as election outcomes;
	(distances and reassignments are computed at the address level.)
	
*/


********************************************************************************
// Prep conversion file: Election x to SE-18  //
*******************************************************************************
	
* READ individual sheets & append

	foreach x in "LTW2013" "BTW2013" "KOW2014" "EUW2014" "BTW2017" "KOW2020" "EUW2019"  {

		import excel using ///
		"$rawdata/statistical_office/Umrechnung_Wahlen_2013-2020_auf_LTW2018.xlsx", ///
				sheet("`x'") cellrange("A4") clear
		
		* clean sheet
		assert C[1] == "Deutsche"
		drop if _n == 1
		drop if A == "Gesamtsumme"
		replace C = subinstr(C, "-","",.)
		replace D = subinstr(D, "-","", .)
		keep A B C D
		destring A B C D, replace
		
		rename A 	sb_ltw2018
		rename B 	sb
		lab var 	sb_ltw2018 "Precinct LTW 2018"
		lab var 	sb 		   "Precinct (election-specific)"
		
		* For the very few missings: ASSUME missing = 0
		replace C = 0 if missing(C)
		replace D = 0 if missing(D)
		
		* Compute Basis of eligible voters [EUW & KOW w/ foreigners, else w/o]
		if inlist("`x'","KOW2014","KOW2020", "EUW2014", "EUW2019"){
			gen wahlb = C + D 	// eligible voters in election x = Germans + EU-Foreigners
		}
		else {
			gen wahlb = C		// eligible voters in election x = only Germans
		}
		
		drop C D
		
		* Compute shares
		
		// gen: total eligible voters by precinct in election x
		bys sb: egen tot_wb = total(wahlb)
		
		// gen: conv_wgt = conversion weight
		gen conv_wgt = wahlb/ tot_wb
		lab var conv_wgt "Conversion key from election x to SE-18"
		
		drop wahlb tot_wb
		
		// gen Election ID
		gen 	wahl = ustrregexra("`x'", "(?=\w*)20(?=\d{2})","")		// removes "20" from e.g., EUW2014 => EUW14
		lab var wahl "Election"
		
		// save
		tempfile conv_`x'_ltw2018
		save  `conv_`x'_ltw2018'
	}
clear

* Append
	foreach x in "LTW2013" "BTW2013" "KOW2014" "EUW2014" "BTW2017" "KOW2020" "EUW2019" {
		append using  `conv_`x'_ltw2018'
	}
	
	
* Strip "0" from precinct ID
	tostring sb_ltw2018 sb, replace
	foreach v of varlist sb_ltw2018 sb {
		replace `v' = ///
				substr(`v',1,1) + substr(`v',3,.) if strlen(`v') == 4
		replace `v' = ///
				substr(`v',1,2) + substr(`v',4,.) if strlen(`v') == 5
	}
	destring sb_ltw2018 sb, replace
	
* Save
	save "$tmp/convkeys_ltw2018.dta", replace
	